package com.kaizeli.website.service.impl;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.kaizeli.website.service.WarningService;
import com.kaizeli.website.config.WebSocketServerConfig;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 预警服务实现类
 * 基于数据库直接查询，不依赖复杂的实体映射
 */
@Slf4j
@Service
public class WarningServiceImpl implements WarningService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private WebSocketServerConfig webSocketServerConfig;

    private final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public void executeAllWarningChecks() {
        log.info("==================== 开始执行预警检查任务 ====================");
        try {
            checkAcceptanceWarnings();
            checkPaymentWarnings();
            checkPersonnelWarnings();
            checkContractWarnings();
            log.info("==================== 预警检查任务完成 ====================");
        } catch (Exception e) {
            log.error("预警检查任务执行失败", e);
        }
    }

    @Override
    public void checkAcceptanceWarnings() {
        log.info(">>> 检查验收预警");
        
        // 规则1: 验收节点已超期 (ACC_OVERDUE)
        checkAcceptanceOverdue();
        
        // 规则2: 验收节点即将到期 (ACC_DUE_SOON)
        checkAcceptanceDueSoon();
        
        // 规则3: 验收待提交超时 (ACC_PENDING_SUBMIT)
        checkAcceptancePendingSubmit();
        
        // 规则4: 验收待审核超时 (ACC_PENDING_REVIEW)
        checkAcceptancePendingReview();
        
        log.info("<<< 验收预警检查完成");
    }

    /**
     * 规则1: 检查验收节点已超期（根据项目优先级设置不同预警级别）
     */
    private void checkAcceptanceOverdue() {
        String sql = "SELECT an.id, an.project_id, an.acceptance_period, an.acceptance_date, an.status, " +
                    "p.priority, p.project_name " +
                    "FROM acceptance_node an " +
                    "JOIN project p ON an.project_id = p.id " +
                    "WHERE an.status != '已验收' " +
                    "AND an.acceptance_date < CURDATE() " +
                    "AND p.is_deleted = 0 " +
                    "AND p.status = 2";  // 只有进行中的项目才触发预警
        
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        log.info("  - 发现超期验收节点: {} 条", list.size());
        
        for (Map<String, Object> node : list) {
            Long id = getLong(node.get("id"));
            LocalDate acceptanceDate = getLocalDate(node.get("acceptance_date"));
            long overdueDays = ChronoUnit.DAYS.between(acceptanceDate, LocalDate.now());
            Integer priority = getInteger(node.get("priority"));
            
            // 根据优先级和超期天数确定预警级别
            String warningLevel = getOverdueWarningLevel(priority, overdueDays);
            String priorityName = getPriorityName(priority);
            
            createWarningIfNotExists(
                "ACC_OVERDUE",
                "acceptance",
                warningLevel,
                id,
                "acceptance_node",
                "验收节点已超期",
                String.format("项目【%s】（优先级：%s）验收节点（周期：%s）已超期 %d 天，预期验收时间：%s，当前状态：%s",
                    node.get("project_name"),
                    priorityName,
                    node.get("acceptance_period"),
                    overdueDays,
                    node.get("acceptance_date"),
                    node.get("status"))
            );
        }
    }

    /**
     * 规则2: 检查验收节点即将到期（根据项目优先级设置不同预警时间）
     * 优先级1（最高）: 提前30天预警
     * 优先级2（高）: 提前21天预警  
     * 优先级3（中）: 提前14天预警
     * 优先级4（低）: 提前10天预警
     * 优先级5（最低）: 提前7天预警
     */
    private void checkAcceptanceDueSoon() {
        // 根据项目优先级设置不同的预警时间
        String sql = "SELECT an.id, an.project_id, an.acceptance_period, an.acceptance_date, an.status, " +
                    "p.priority, p.project_name " +
                    "FROM acceptance_node an " +
                    "JOIN project p ON an.project_id = p.id " +
                    "WHERE an.status != '已验收' " +
                    "AND p.is_deleted = 0 " +
                    "AND p.status = 2 " +  // 只有进行中的项目才触发预警
                    "AND (" +
                    "  (p.priority = 1 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)) OR " +
                    "  (p.priority = 2 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY)) OR " +
                    "  (p.priority = 3 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)) OR " +
                    "  (p.priority = 4 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) OR " +
                    "  (p.priority = 5 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)) OR " +
                    "  (p.priority IS NULL AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY))" +
                    ")";
        
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        log.info("  - 发现即将到期验收节点: {} 条", list.size());
        
        for (Map<String, Object> node : list) {
            Long id = getLong(node.get("id"));
            LocalDate acceptanceDate = getLocalDate(node.get("acceptance_date"));
            long daysLeft = ChronoUnit.DAYS.between(LocalDate.now(), acceptanceDate);
            Integer priority = getInteger(node.get("priority"));
            
            // 根据优先级确定预警级别
            String warningLevel = getWarningLevelByPriority(priority, daysLeft);
            String priorityName = getPriorityName(priority);
            
            createWarningIfNotExists(
                "ACC_DUE_SOON",
                "acceptance",
                warningLevel,
                id,
                "acceptance_node",
                "验收节点即将到期",
                String.format("项目【%s】（优先级：%s）验收节点（周期：%s）还有 %d 天到期，验收日期：%s",
                    node.get("project_name"),
                    priorityName,
                    node.get("acceptance_period"),
                    daysLeft,
                    node.get("acceptance_date"))
            );
        }
    }

    /**
     * 根据项目优先级和剩余天数确定预警级别
     */
    private String getWarningLevelByPriority(Integer priority, long daysLeft) {
        if (priority == null) priority = 3; // 默认中等优先级
        
        // 根据优先级和剩余天数确定预警级别
        if (priority == 1) { // 最高优先级
            if (daysLeft <= 7) return "critical";
            if (daysLeft <= 15) return "warning";
            return "info";
        } else if (priority == 2) { // 高优先级
            if (daysLeft <= 5) return "critical";
            if (daysLeft <= 10) return "warning";
            return "info";
        } else if (priority == 3) { // 中等优先级
            if (daysLeft <= 3) return "critical";
            if (daysLeft <= 7) return "warning";
            return "info";
        } else if (priority == 4) { // 低优先级
            if (daysLeft <= 2) return "critical";
            if (daysLeft <= 5) return "warning";
            return "info";
        } else if (priority == 5) { // 最低优先级
            if (daysLeft <= 1) return "critical";
            if (daysLeft <= 3) return "warning";
            return "info";
        }
        
        return "info"; // 默认
    }
    
    /**
     * 根据项目优先级和超期天数确定预警级别
     */
    private String getOverdueWarningLevel(Integer priority, long overdueDays) {
        if (priority == null) priority = 3; // 默认中等优先级
        
        // 根据优先级和超期天数确定预警级别
        if (priority == 1) { // 最高优先级 - 超期1天就是critical
            if (overdueDays >= 1) return "critical";
            return "warning";
        } else if (priority == 2) { // 高优先级 - 超期3天是critical
            if (overdueDays >= 3) return "critical";
            if (overdueDays >= 1) return "warning";
            return "info";
        } else if (priority == 3) { // 中等优先级 - 超期7天是critical
            if (overdueDays >= 7) return "critical";
            if (overdueDays >= 3) return "warning";
            return "info";
        } else if (priority == 4) { // 低优先级 - 超期15天是critical
            if (overdueDays >= 15) return "critical";
            if (overdueDays >= 7) return "warning";
            return "info";
        } else if (priority == 5) { // 最低优先级 - 超期30天是critical
            if (overdueDays >= 30) return "critical";
            if (overdueDays >= 15) return "warning";
            return "info";
        }
        
        return "warning"; // 默认
    }
    
    /**
     * 获取优先级名称
     */
    private String getPriorityName(Integer priority) {
        if (priority == null) return "中";
        
        switch (priority) {
            case 1: return "最高";
            case 2: return "高";
            case 3: return "中";
            case 4: return "低";
            case 5: return "最低";
            default: return "中";
        }
    }

    /**
     * 规则3: 检查验收待提交超时（7天）
     */
    private void checkAcceptancePendingSubmit() {
        String sql = "SELECT an.id, an.project_id, an.acceptance_period, an.status, an.create_time, " +
                    "p.project_name " +
                    "FROM acceptance_node an " +
                    "JOIN project p ON an.project_id = p.id " +
                    "WHERE an.status = '待提交' " +
                    "AND an.create_time < DATE_SUB(NOW(), INTERVAL 7 DAY) " +
                    "AND p.is_deleted = 0 " +
                    "AND p.status = 2";  // 只有进行中的项目才触发预警
        
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        log.info("  - 发现待提交超时验收节点: {} 条", list.size());
        
        for (Map<String, Object> node : list) {
            Long id = getLong(node.get("id"));
            
            createWarningIfNotExists(
                "ACC_PENDING_SUBMIT",
                "acceptance",
                "info",
                id,
                "acceptance_node",
                "验收待提交超时",
                String.format("项目【%s】验收节点（周期：%s）已创建超过7天，仍处于待提交状态，请尽快填写并提交",
                    node.get("project_name"),
                    node.get("acceptance_period"))
            );
        }
    }

    /**
     * 规则4: 检查验收待审核超时（3天）
     */
    private void checkAcceptancePendingReview() {
        String sql = "SELECT an.id, an.project_id, an.acceptance_period, an.status, an.update_time, " +
                    "p.project_name " +
                    "FROM acceptance_node an " +
                    "JOIN project p ON an.project_id = p.id " +
                    "WHERE an.status = '待审核' " +
                    "AND an.update_time < DATE_SUB(NOW(), INTERVAL 3 DAY) " +
                    "AND p.is_deleted = 0 " +
                    "AND p.status = 2";  // 只有进行中的项目才触发预警
        
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        log.info("  - 发现待审核超时验收节点: {} 条", list.size());
        
        for (Map<String, Object> node : list) {
            Long id = getLong(node.get("id"));
            
            createWarningIfNotExists(
                "ACC_PENDING_REVIEW",
                "acceptance",
                "warning",
                id,
                "acceptance_node",
                "验收待审核超时",
                String.format("项目【%s】验收节点（周期：%s）已提交超过3天，仍未审核，请及时处理",
                    node.get("project_name"),
                    node.get("acceptance_period"))
            );
        }
    }

    @Override
    public void checkPaymentWarnings() {
        log.info(">>> 检查回款预警");
        
        checkPaymentOverdue();
        checkPaymentDueSoon();
        
        log.info("<<< 回款预警检查完成");
    }
    
    /**
     * 检查回款已逾期
     */
    private void checkPaymentOverdue() {
        String sql = "SELECT p.id, p.project_id, p.amount, p.payment_date, p.payment_status, p.remaining_amount, " +
                    "pr.project_name, pr.priority, DATEDIFF(CURDATE(), p.payment_date) as overdue_days " +
                    "FROM payment p " +
                    "JOIN project pr ON p.project_id = pr.id " +
                    "WHERE p.payment_status != '已结清' " +
                    "AND p.payment_date < CURDATE() " +
                    "AND pr.is_deleted = 0 " +
                    "AND pr.status = 2";  // 只有进行中的项目才触发预警
        
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            log.info("  - 发现已逾期回款: {} 条", list.size());
            
            for (Map<String, Object> payment : list) {
                Long id = getLong(payment.get("id"));
                Integer priority = getInteger(payment.get("priority"));
                Long overdueDays = getLong(payment.get("overdue_days"));
                
                String warningLevel = getPaymentOverdueWarningLevel(priority, overdueDays);
                String priorityName = getPriorityName(priority);
                
                createWarningIfNotExists(
                    "PAY_OVERDUE",
                    "payment",
                    warningLevel,
                    id,
                    "payment",
                    "回款已逾期",
                    String.format("项目【%s】（优先级：%s）回款已逾期 %d 天，应收日期：%s，应收金额：%.2f，剩余待回款：%.2f",
                        payment.get("project_name"),
                        priorityName,
                        overdueDays,
                        payment.get("payment_date"),
                        payment.get("amount"),
                        payment.get("remaining_amount"))
                );
            }
        } catch (Exception e) {
            log.warn("  - 回款逾期预警检查失败：{}", e.getMessage());
        }
    }
    
    /**
     * 检查回款即将到期
     */
    private void checkPaymentDueSoon() {
        String sql = "SELECT p.id, p.project_id, p.amount, p.payment_date, p.payment_status, p.remaining_amount, " +
                    "pr.project_name, pr.priority, DATEDIFF(p.payment_date, CURDATE()) as days_left " +
                    "FROM payment p " +
                    "JOIN project pr ON p.project_id = pr.id " +
                    "WHERE p.payment_status != '已结清' " +
                    "AND pr.is_deleted = 0 " +
                    "AND pr.status = 2 " +
                    "AND (" +
                    "  (pr.priority = 1 AND p.payment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)) OR " +
                    "  (pr.priority = 2 AND p.payment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY)) OR " +
                    "  (pr.priority = 3 AND p.payment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)) OR " +
                    "  (pr.priority = 4 AND p.payment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) OR " +
                    "  (pr.priority = 5 AND p.payment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)) OR " +
                    "  (pr.priority IS NULL AND p.payment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY))" +
                    ")";
        
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            log.info("  - 发现即将到期回款: {} 条", list.size());
            
            for (Map<String, Object> payment : list) {
                Long id = getLong(payment.get("id"));
                Integer priority = getInteger(payment.get("priority"));
                Long daysLeft = getLong(payment.get("days_left"));
                
                String warningLevel = getWarningLevelByPriority(priority, daysLeft);
                String priorityName = getPriorityName(priority);
                
                createWarningIfNotExists(
                    "PAY_DUE_SOON",
                    "payment",
                    warningLevel,
                    id,
                    "payment",
                    "回款即将到期",
                    String.format("项目【%s】（优先级：%s）回款还有 %d 天到期，应收日期：%s，应收金额：%.2f，剩余待回款：%.2f",
                        payment.get("project_name"),
                        priorityName,
                        daysLeft,
                        payment.get("payment_date"),
                        payment.get("amount"),
                        payment.get("remaining_amount"))
                );
            }
        } catch (Exception e) {
            log.warn("  - 回款即将到期预警检查失败：{}", e.getMessage());
        }
    }
    
    /**
     * 根据项目优先级和回款逾期天数确定预警级别
     */
    private String getPaymentOverdueWarningLevel(Integer priority, long overdueDays) {
        if (priority == null) priority = 3;
        
        if (priority == 1) {
            if (overdueDays >= 1) return "critical";
            return "warning";
        } else if (priority == 2) {
            if (overdueDays >= 5) return "critical";
            if (overdueDays >= 1) return "warning";
            return "info";
        } else if (priority == 3) {
            if (overdueDays >= 10) return "critical";
            if (overdueDays >= 5) return "warning";
            return "info";
        } else if (priority == 4) {
            if (overdueDays >= 20) return "critical";
            if (overdueDays >= 10) return "warning";
            return "info";
        } else if (priority == 5) {
            if (overdueDays >= 30) return "critical";
            if (overdueDays >= 15) return "warning";
            return "info";
        }
        
        return "warning";
    }

    @Override
    public void checkPersonnelWarnings() {
        log.info(">>> 检查人员预警");
        
        checkPersonnelWorkExpiring();
        
        log.info("<<< 人员预警检查完成");
    }
    
    /**
     * 检查人员工作安排即将到期
     */
    private void checkPersonnelWorkExpiring() {
        // 使用employee_acceptance表检查人员工作安排
        String sql = "SELECT ea.id, ea.project_id, ea.employee_id, e.name as employee_name, " +
                    "an.acceptance_date, p.project_name, p.priority, " +
                    "DATEDIFF(an.acceptance_date, CURDATE()) as days_left " +
                    "FROM employee_acceptance ea " +
                    "JOIN acceptance_node an ON ea.acceptance_id = an.id " +
                    "JOIN project p ON ea.project_id = p.id " +
                    "LEFT JOIN employee e ON ea.employee_id = e.id " +
                    "WHERE p.is_deleted = 0 " +
                    "AND p.status = 2 " +
                    "AND an.status != '已验收' " +
                    "AND (" +
                    "  (p.priority = 1 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)) OR " +
                    "  (p.priority = 2 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY)) OR " +
                    "  (p.priority = 3 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)) OR " +
                    "  (p.priority = 4 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) OR " +
                    "  (p.priority = 5 AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)) OR " +
                    "  (p.priority IS NULL AND an.acceptance_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY))" +
                    ")";
        
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            log.info("  - 发现即将到期人员工作安排: {} 条", list.size());
            
            for (Map<String, Object> work : list) {
                Long id = getLong(work.get("id"));
                Integer priority = getInteger(work.get("priority"));
                Long daysLeft = getLong(work.get("days_left"));
                
                String warningLevel = getWarningLevelByPriority(priority, daysLeft);
                String priorityName = getPriorityName(priority);
                
                createWarningIfNotExists(
                    "PER_WORK_EXPIRING",
                    "personnel",
                    warningLevel,
                    id,
                    "employee_acceptance",
                    "人员工作安排即将到期",
                    String.format("项目【%s】（优先级：%s）人员【%s】的工作安排还有 %d 天到期，验收日期：%s",
                        work.get("project_name"),
                        priorityName,
                        work.get("employee_name"),
                        daysLeft,
                        work.get("acceptance_date"))
                );
            }
        } catch (Exception e) {
            log.warn("  - 人员工作安排预警检查失败：{}", e.getMessage());
        }
    }

    @Override
    public void checkContractWarnings() {
        log.info(">>> 检查合同预警");
        
        checkProjectContractExpiring();
        checkProjectContractOverdue();
        
        log.info("<<< 合同预警检查完成");
    }
    
    /**
     * 检查项目合同即将到期
     */
    private void checkProjectContractExpiring() {
        String sql = "SELECT p.id, p.project_code, p.project_name, p.end_date, p.priority, p.status, " +
                    "DATEDIFF(p.end_date, CURDATE()) as days_left " +
                    "FROM project p " +
                    "WHERE p.is_deleted = 0 " +
                    "AND p.status = 2 " +
                    "AND (" +
                    "  (p.priority = 1 AND p.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)) OR " +
                    "  (p.priority = 2 AND p.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY)) OR " +
                    "  (p.priority = 3 AND p.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)) OR " +
                    "  (p.priority = 4 AND p.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) OR " +
                    "  (p.priority = 5 AND p.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)) OR " +
                    "  (p.priority IS NULL AND p.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY))" +
                    ")";
        
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            log.info("  - 发现即将到期项目合同: {} 条", list.size());
            
            for (Map<String, Object> project : list) {
                Long id = getLong(project.get("id"));
                Integer priority = getInteger(project.get("priority"));
                Long daysLeft = getLong(project.get("days_left"));
                
                String warningLevel = getWarningLevelByPriority(priority, daysLeft);
                String priorityName = getPriorityName(priority);
                
                createWarningIfNotExists(
                    "CON_PROJECT_EXPIRING",
                    "contract",
                    warningLevel,
                    id,
                    "project",
                    "项目合同即将到期",
                    String.format("项目【%s】（优先级：%s）合同还有 %d 天到期，结束日期：%s，请及时跟进验收和回款",
                        project.get("project_name"),
                        priorityName,
                        daysLeft,
                        project.get("end_date"))
                );
            }
        } catch (Exception e) {
            log.warn("  - 项目合同即将到期预警检查失败：{}", e.getMessage());
        }
    }
    
    /**
     * 检查项目合同已超期
     */
    private void checkProjectContractOverdue() {
        String sql = "SELECT p.id, p.project_code, p.project_name, p.end_date, p.priority, p.status, " +
                    "DATEDIFF(CURDATE(), p.end_date) as overdue_days " +
                    "FROM project p " +
                    "WHERE p.is_deleted = 0 " +
                    "AND p.status = 2 " +
                    "AND p.end_date < CURDATE()";
        
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            log.info("  - 发现已超期项目合同: {} 条", list.size());
            
            for (Map<String, Object> project : list) {
                Long id = getLong(project.get("id"));
                Integer priority = getInteger(project.get("priority"));
                Long overdueDays = getLong(project.get("overdue_days"));
                
                String warningLevel = getContractOverdueWarningLevel(priority, overdueDays);
                String priorityName = getPriorityName(priority);
                
                createWarningIfNotExists(
                    "CON_PROJECT_OVERDUE",
                    "contract",
                    warningLevel,
                    id,
                    "project",
                    "项目合同已超期",
                    String.format("项目【%s】（优先级：%s）合同已超期 %d 天，预期结束日期：%s，项目仍在进行中，存在风险！",
                        project.get("project_name"),
                        priorityName,
                        overdueDays,
                        project.get("end_date"))
                );
            }
        } catch (Exception e) {
            log.warn("  - 项目合同超期预警检查失败：{}", e.getMessage());
        }
    }
    
    /**
     * 根据项目优先级和合同超期天数确定预警级别
     */
    private String getContractOverdueWarningLevel(Integer priority, long overdueDays) {
        if (priority == null) priority = 3;
        
        if (priority == 1) {
            if (overdueDays >= 3) return "critical";
            if (overdueDays >= 1) return "warning";
            return "info";
        } else if (priority == 2) {
            if (overdueDays >= 7) return "critical";
            if (overdueDays >= 3) return "warning";
            return "info";
        } else if (priority == 3) {
            if (overdueDays >= 14) return "critical";
            if (overdueDays >= 7) return "warning";
            return "info";
        } else if (priority == 4) {
            if (overdueDays >= 21) return "critical";
            if (overdueDays >= 14) return "warning";
            return "info";
        } else if (priority == 5) {
            if (overdueDays >= 30) return "critical";
            if (overdueDays >= 21) return "warning";
            return "info";
        }
        
        return "warning";
    }

    @Override
    public void closeWarningsForAcceptance(Long acceptanceId) {
        String sql = "UPDATE warning_records " +
                    "SET status = 'auto_closed', handled_time = NOW(), " +
                    "handle_remark = '验收节点已通过审核，自动关闭预警' " +
                    "WHERE related_table = 'acceptance_node' " +
                    "AND related_id = ? " +
                    "AND status = 'active'";
        
        int count = jdbcTemplate.update(sql, acceptanceId);
        if (count > 0) {
            log.info("自动关闭验收节点 {} 的 {} 条预警", acceptanceId, count);
        }
    }

    /**
     * 创建预警记录（避免重复），并通过WebSocket推送
     */
    private void createWarningIfNotExists(String ruleCode, String warningType, 
                                         String warningLevel, Long relatedId, 
                                         String relatedTable, String title, String content) {
        try {
            // 检查24小时内是否已存在相同预警（避免重复推送）
            String checkSql = "SELECT COUNT(*) FROM warning_records " +
                            "WHERE rule_code = ? " +
                            "AND related_id = ? " +
                            "AND related_table = ? " +
                            "AND status = 'active' " +
                            "AND warning_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)";
            
            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, 
                ruleCode, relatedId, relatedTable);
            
            if (count != null && count > 0) {
                log.debug("    预警已存在，跳过：{} - ID:{}", title, relatedId);
                return;
            }
            
            // 生成预警编号
            String warningNo = generateWarningNo(warningType);
            
            // 插入新预警记录
            String insertSql = "INSERT INTO warning_records " +
                             "(warning_no, rule_code, warning_type, warning_level, related_id, related_table, " +
                             "warning_title, warning_content, warning_time, status) " +
                             "VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), 'active')";
            
            jdbcTemplate.update(insertSql, warningNo, ruleCode, warningType, warningLevel, 
                relatedId, relatedTable, title, content);
            
            log.info("    ✓ 创建预警：{} [{}] - ID:{}", title, warningLevel, relatedId);
            
            // 【新增】通过WebSocket实时推送预警消息
            pushWarningToClients(warningNo, ruleCode, warningType, warningLevel, 
                               relatedId, relatedTable, title, content);
            
        } catch (Exception e) {
            log.error("创建预警失败：{}", e.getMessage());
        }
    }

    /**
     * 通过WebSocket推送预警消息到所有在线客户端
     */
    private void pushWarningToClients(String warningNo, String ruleCode, String warningType,
                                     String warningLevel, Long relatedId, String relatedTable,
                                     String title, String content) {
        try {
            // 构建推送消息
            Map<String, Object> warningMessage = new HashMap<>();
            warningMessage.put("type", "new_warning");  // 消息类型：新预警
            warningMessage.put("warningNo", warningNo);
            warningMessage.put("ruleCode", ruleCode);
            warningMessage.put("warningType", warningType);
            warningMessage.put("warningLevel", warningLevel);
            warningMessage.put("relatedId", relatedId);
            warningMessage.put("relatedTable", relatedTable);
            warningMessage.put("warningTitle", title);
            warningMessage.put("warningContent", content);
            warningMessage.put("warningTime", LocalDateTime.now().format(DateTimeFormatter.ISO_DATE_TIME));
            warningMessage.put("timestamp", System.currentTimeMillis());
            
            // 转换为JSON字符串
            String jsonMessage = objectMapper.writeValueAsString(warningMessage);
            
            // 广播给所有在线客户端
            webSocketServerConfig.broadcastMessage(jsonMessage);
            
            log.info("✅ 预警消息已推送 - 编号: {}, 标题: {}", warningNo, title);
            
        } catch (Exception e) {
            log.error("❌ 推送预警消息失败：{}", e.getMessage(), e);
        }
    }

    /**
     * 生成预警编号
     */
    private String generateWarningNo(String warningType) {
        String prefix = "W-" + warningType.substring(0, 3).toUpperCase() + "-";
        String timestamp = String.valueOf(System.currentTimeMillis());
        return prefix + timestamp;
    }

    /**
     * 辅助方法：安全获取Long值
     */
    private Long getLong(Object value) {
        if (value == null) return null;
        if (value instanceof Long) return (Long) value;
        if (value instanceof Integer) return ((Integer) value).longValue();
        return Long.parseLong(value.toString());
    }

    /**
     * 辅助方法：安全获取LocalDate
     */
    private LocalDate getLocalDate(Object value) {
        if (value == null) return null;
        if (value instanceof LocalDate) return (LocalDate) value;
        if (value instanceof java.sql.Date) {
            return ((java.sql.Date) value).toLocalDate();
        }
        return LocalDate.parse(value.toString());
    }
    
    /**
     * 辅助方法：安全获取Integer值
     */
    private Integer getInteger(Object value) {
        if (value == null) return null;
        if (value instanceof Integer) return (Integer) value;
        if (value instanceof Long) return ((Long) value).intValue();
        return Integer.parseInt(value.toString());
    }
    
    @Override
    public void clearAllWarnings() {
        log.info("==================== 开始清除所有预警信息 ====================");
        try {
            // 1. 查询当前预警数量
            String countSql = "SELECT COUNT(*) FROM warning_records";
            Integer count = jdbcTemplate.queryForObject(countSql, Integer.class);
            log.info("数据库中现有预警记录数: {}", count);
            
            // 2. 删除数据库中的所有预警记录
            String deleteSql = "DELETE FROM warning_records";
            int deletedCount = jdbcTemplate.update(deleteSql);
            log.info("✓ 已清除数据库中的预警记录: {} 条", deletedCount);
            
            // 3. 重置自增ID（可选，保持数据整洁）
            try {
                String resetSql = "ALTER TABLE warning_records AUTO_INCREMENT = 1";
                jdbcTemplate.execute(resetSql);
                log.info("✓ 已重置预警记录表自增ID");
            } catch (Exception e) {
                log.warn("重置自增ID失败（可忽略）: {}", e.getMessage());
            }
            
            // 4. 通过WebSocket通知所有客户端预警已清除
            pushClearWarningsNotification();
            
            log.info("==================== 预警清除完成 ====================");
            log.info("✅ 成功清除 {} 条预警记录", deletedCount);
            log.info("💡 下次触发预警检查时将重新生成预警");
            
        } catch (Exception e) {
            log.error("❌ 清除预警失败", e);
            throw new RuntimeException("清除预警失败: " + e.getMessage());
        }
    }
    
    /**
     * 通过WebSocket推送清除预警的通知
     */
    private void pushClearWarningsNotification() {
        try {
            // 构建清除通知消息
            Map<String, Object> clearMessage = new HashMap<>();
            clearMessage.put("type", "clear_warnings");  // 消息类型：清除预警
            clearMessage.put("message", "所有预警已被清除");
            clearMessage.put("clearedTime", LocalDateTime.now().format(DateTimeFormatter.ISO_DATE_TIME));
            clearMessage.put("timestamp", System.currentTimeMillis());
            
            // 转换为JSON字符串
            String jsonMessage = objectMapper.writeValueAsString(clearMessage);
            
            // 广播给所有在线客户端
            webSocketServerConfig.broadcastMessage(jsonMessage);
            
            log.info("✅ 已通过WebSocket推送清除通知给 {} 个在线客户端", 
                    webSocketServerConfig.getConnectionCount());
            
        } catch (Exception e) {
            log.error("❌ 推送清除通知失败：{}", e.getMessage(), e);
        }
    }
}

